CREATE TABLE categories(
    _id INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    sort INTEGER NOT NULL,
    flags INTEGER NOT NULL
);

-- Insert system category
INSERT OR IGNORE INTO categories(_id, name, sort, flags) VALUES (0, "", -1, 0);
-- Disallow deletion of default category
CREATE TRIGGER IF NOT EXISTS system_category_delete_trigger BEFORE DELETE
ON categories
BEGIN SELECT CASE
    WHEN old._id <= 0 THEN
        RAISE(ABORT, "System category can't be deleted")
    END;
END;

getCategory:
SELECT *
FROM categories
WHERE _id = :id
LIMIT 1;

getCategories:
SELECT
_id AS id,
name,
sort AS `order`,
flags
FROM categories
ORDER BY sort;

getCategoriesByMangaId:
SELECT
C._id AS id,
C.name,
C.sort AS `order`,
C.flags
FROM categories C
JOIN mangas_categories MC
ON C._id = MC.category_id
WHERE MC.manga_id = :mangaId;

insert:
INSERT INTO categories(name, sort, flags)
VALUES (:name, :order, :flags);

delete:
DELETE FROM categories
WHERE _id = :categoryId;

update:
UPDATE categories
SET name = coalesce(:name, name),
    sort = coalesce(:order, sort),
    flags = coalesce(:flags, flags)
WHERE _id = :categoryId;

updateAllFlags:
UPDATE categories SET
flags = coalesce(?, flags);

selectLastInsertedRowId:
SELECT last_insert_rowid();